2
2
.
.
1
1
5
5
D
D
B
B
Q
Q
u
u
e
e
r
r
i
i
e
e
s
s
I
I
n
n
f
f
o
o
[
[
R
R
]
]
Following tutorials show how to use @Query Annotation to execute DB Queries.
You simply add @Query to a Method and when Method is called it will execute specified SQL and return its result.
SQL can be provided as
● Native Query SELECT * FROM PERSON WHERE NAME='John' AND AGE=20 (nativeQuery = true)
● JPQL SELECT john FROM Person john WHERE john.name = 'John' AND john.age = 20 (default)
Method can then be called like this
● Person person = personRepository.getPersonByNameAgeIndexed(name, age); (return single Record)
● List<Person> persons = personRepository.getPersonsByName(name); (returns List of Records)
If SQL has Parameters, Method will also have Input Parameters
● Indexed Parameters (?1, ?2) => ( String name, int age)
● Named Parameters (:parname, :parage) => (@Param("parname") String name, @Param("parage") int age)
Examples of Native Query Methods
//=======================================================================================
// SELECT
//=======================================================================================
//NO PARAMETERS
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = 'John' AND AGE = 20")
Person getJohn();
//INDEXED PARAMETERS
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = ?1 AND AGE = ?2")
Person selectPersonByNameAgeIndexed(String name, Integer age);
//NAMED PARAMETERS
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = :name AND AGE = :parameterAge")
Person selectPersonByNameAgeNamed(String name, @Param("parameterAge") Integer age);
//RETURN LIST
@Query(nativeQuery = true, value = "SELECT * FROM PERSON WHERE NAME = :name")
List<Person> selectPersonsByName(String name);
//=======================================================================================
// UPDATE
//=======================================================================================
@Modifying
@Query(nativeQuery = true, value = "UPDATE PERSON SET AGE = :newAge WHERE NAME = :name")
Integer updatePersonsByName(String name, Integer newAge);
//=======================================================================================
// DELETE
//=======================================================================================
@Modifying
@Query(nativeQuery = true, value = "DELETE FROM PERSON WHERE NAME = :name")
Integer deletePersonsByName(String name);
//=======================================================================================
// INSERT
//=======================================================================================
@Modifying
@Query(nativeQuery = true, value = "INSERT INTO PERSON (name, age) VALUES (:name, :age)")
Integer insertPerson(String name, Integer age);
Examples of JPQL Methods
//=======================================================================================
// SELECT
//=======================================================================================
//NO PARAMETERS
@Query(value = "SELECT john FROM Person john WHERE john.name = 'John' AND john.age = 20")
Person selectJohn();
//INDEXED PARAMETERS
@Query(value = "SELECT person FROM Person person WHERE person.name = ?1 AND person.age = ?2")
Person selectPersonByNameAgeIndexed(String name, Integer age);
//NAMED PARAMETERS
@Query(value = "SELECT person FROM Person person WHERE person.name = :name AND person.age = :parameterAge")
Person selectPersonByNameAgeNamed(String name, @Param("parameterAge") Integer age);
//RETURN LIST
@Query(value = "SELECT person FROM Person person WHERE person.name = :name")
List<Person> selectPersonsByName(String name);
//RETURN SORTED LIST
@Query(value = "SELECT person FROM Person person WHERE person.name = :name")
List<Person> selectPersonsByNameSorted(String name, Sort sort);
//=======================================================================================
// UPDATE
//=======================================================================================
@Modifying
@Query(value = "UPDATE Person person SET person.age = :newAge WHERE person.name = :name")
Integer updatePersonsByName(String name, Integer newAge);
//=======================================================================================
// DELETE
//=======================================================================================
@Modifying
@Query(value = "DELETE FROM Person person WHERE person.name = :name")
Integer deletePersonsByName(String name);
//=======================================================================================
// INSERT IS NOT SUPPORTED BY JPA
//=======================================================================================
Examples of Method Calls
Person person = personRepository.selectPerson (name); //Returns single Record
List<Person> persons = personRepository.selectPersons(name); //Returns multiple Records
Integer recordsUpdated = personRepository.updatePersons(name, newAge); //Returns affected number
